package com.glsc.ngateway.common.base.repo.mysql.gateway.platform.it;


import com.glsc.ngateway.common.base.domain.mysql.gateway.platform.it.ItCloudDevice;
import com.glsc.ngateway.common.base.domain.mysql.gateway.platform.it.ItVirtualDevice;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.*;


@Repository
public interface ItVirtualDeviceRepo extends JpaRepository<ItVirtualDevice, String> {

    ItVirtualDevice findByDevId(String devId);

    @Query(value = "select m from ItVirtualDevice m where 1=1 and (:userid is null OR m.userId = :userid) and m.mark='1' order by m.insertTime desc")
    Page<ItVirtualDevice> getVirtualDeviceByUser(Pageable pageable, String userid);


    @Query(value = "select m from ItVirtualDevice m left join SystemMain n on m.businessName = n.systemClassNo where 1=1 and m.mark='1' " +
            "and (:businessName is null OR m.businessName = :businessName )  " +
            "and (:applicationDetail is null OR m.applicationDetail = :applicationDetail )  " +
            "and (:opStyle is null OR n.mainClass = :opStyle )  " +
            "and (:machineArea is null OR m.machineArea = :machineArea)  " +
            "and (:virtualIp is null OR m.virtualIp = :virtualIp )  " +
            "and (:operationSys is null OR m.operationSys = :operationSys )  " +
            "and (:qj is null OR m.qj = :qj )  " +
            "and (:devId is null OR m.devId = :devId )  " +
            "and (:userId is null OR m.userId = :userId)  " +
            "and (:virtualName is null OR m.virtualName LIKE CONCAT('%',:virtualName,'%'))  " +
            "and (:ip1 is null OR m.ip1 LIKE CONCAT('%',:ip1,'%'))  " +
            "and (:ip2 is null OR m.ip2 LIKE CONCAT('%',:ip2,'%'))  " +
            "and (:vcentIp is null OR m.vcentIp LIKE CONCAT('%',:vcentIp,'%'))  " +
            "and (:searchMacParam is null OR m.mac1 LIKE CONCAT('%',:searchMacParam,'%') OR m.mac2 LIKE CONCAT('%',:searchMacParam,'%'))  " +
            "order by m.insertTime desc ")
    Page<ItVirtualDevice> searchVirtualDevice(Pageable pageable, String businessName, String applicationDetail, String opStyle,
                                              String machineArea, String virtualName, String ip1, String ip2, String vcentIp,
                                              Integer virtualIp, String operationSys, String qj, String userId, String searchMacParam,String devId);

    @Query(value = "select a from ItVirtualDevice a  where mark = '1' "+
            "and ((:ssd is null or ip1 like CONCAT('%',:ssd,'%')) " +
            "or (:ssd is null or ip2 like CONCAT('%',:ssd,'%')) " +
            "or (:ssd is null or mac1 like CONCAT('%',:ssd,'%')) " +
            "or (:ssd is null or mac2 like CONCAT('%',:ssd,'%'))) ")
    List<ItVirtualDevice> searchVirtualDeviceSpecial(String ssd);

    @Query(value = "select a from ItVirtualDevice a  where mark = '1' "+
            "and ((:ssd is null or ip1 = :ssd ) " +
            "or (:ssd is null or ip2   = :ssd ) " +
            "or (:ssd is null or mac1  = :ssd ) " +
            "or (:ssd is null or mac2  = :ssd )) ")
    List<ItVirtualDevice> searchVirtualDeviceSpecialNotLike(String ssd);

    @Query(value = "select m from ItVirtualDevice m where 1=1 " +
            "and (m.businessName = :systemClassNo " +
            "and m.userId = :systemUserId " +
            "and m.mark = 1 " +
            "and m.devId != :devId) ")
    List<ItVirtualDevice> queryBySystemClassNoAndUserId(String systemClassNo, String systemUserId, String devId);

    @Query(value = "select m from ItVirtualDevice m where 1=1 " +
            "and (m.businessName = :systemClassNo " +
            "and m.userId = :systemUserId " +
            "and m.mark = 1 )")
    List<ItVirtualDevice> queryBySystemClassNoAndUserIdWithOutId(String systemClassNo, String systemUserId);


    @Query(value = " select u from  ItVirtualDevice u where 1= 1 " +
            " and u.devId in :ids ")
    List<ItVirtualDevice> queryByIds(Set<String> ids);

}
